-- 12.03.2017 Alexey Mulyukin: Add new fields for store information about model execution statistics

ALTER TABLE `model_stat`
	ADD COLUMN `AvgExecutionTimeInSec` BIGINT(20) NOT NULL COMMENT 'The average time in seconds was spent for run this model' AFTER `MaxExecutionTimeInSec`,
	ADD COLUMN `MinDataSizeInBytes` BIGINT(20) NOT NULL COMMENT 'The minimum size of data for model executions' AFTER `AvgExecutionTimeInSec`,
	ADD COLUMN `MaxDataSizeInBytes` BIGINT(20) NOT NULL COMMENT 'The maximum size of data for model executions' AFTER `MinDataSizeInBytes`,
	ADD COLUMN `AvgDataSizeInBytes` BIGINT(20) NOT NULL COMMENT 'The average size of data for model executions' AFTER `MaxDataSizeInBytes`;

ALTER TABLE `model_stat`
	DROP FOREIGN KEY `FK_Models_Models_Runs_Stats_ModelId`;

ALTER TABLE `model_stat`
	ADD CONSTRAINT `FK_ModelStat_Model_Id` FOREIGN KEY (`ModelId`) REFERENCES `model` (`Id`) ON UPDATE NO ACTION ON DELETE CASCADE;
	
ALTER TABLE `model_stat`
	ALTER `MinExecutionTimeInSec` DROP DEFAULT,
	ALTER `MaxExecutionTimeInSec` DROP DEFAULT,
	ALTER `AvgExecutionTimeInSec` DROP DEFAULT;
ALTER TABLE `model_stat`
	CHANGE COLUMN `MinExecutionTimeInSec` `MinExecutionTimeInMilliseconds` BIGINT(20) NOT NULL COMMENT 'The minimum time in milliseconds was spent for run this model' AFTER `TotalFailedRunCount`,
	CHANGE COLUMN `MaxExecutionTimeInSec` `MaxExecutionTimeInMilliseconds` BIGINT(20) NOT NULL COMMENT 'The maximum time in milliseconds was spent for run this model' AFTER `MinExecutionTimeInMilliseconds`,
	CHANGE COLUMN `AvgExecutionTimeInSec` `AvgExecutionTimeInMilliseconds` BIGINT(20) NOT NULL COMMENT 'The average time in milliseconds was spent for run this model' AFTER `MaxExecutionTimeInMilliseconds`;
	
	
-- 06.04.2017 Alexey Mulyukin: Extend models db structure for creating the gallery page

ALTER TABLE `model`
	ADD COLUMN `OriginalModelId` INT(11) NULL DEFAULT NULL COMMENT 'The model indetifier to original model from public gallery' AFTER `Id`,
	ADD COLUMN `IsPublic` TINYINT(1) NOT NULL DEFAULT '0' COMMENT 'Indicates that this model should be shown at gallery' AFTER `FileName`,
	ADD CONSTRAINT `FK_Models_Models_OriginalModelId` FOREIGN KEY (`OriginalModelId`) REFERENCES `model` (`Id`) ON UPDATE NO ACTION ON DELETE SET NULL;

CREATE TABLE `tag` (
	`Id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'The tag identifier',
	`Name` CHAR(16) NOT NULL COMMENT 'The url part name',
	`Title` VARCHAR(64) NOT NULL COMMENT 'The display name',
	PRIMARY KEY (`Id`),
	INDEX `IX_Tag_Name` (`Name`)
)
COMMENT='Table that contains the information about tags'
ENGINE=InnoDB
;

CREATE TABLE `model_tag` (
	`ModelId` INT(11) NOT NULL COMMENT 'The model identifier',
	`TagId` INT(11) NOT NULL COMMENT 'The tag identifier',
	PRIMARY KEY (`ModelId`, `TagId`),
	INDEX `FK_ModelTag_TagId` (`TagId`),
	CONSTRAINT `FK_ModelTag_ModelId` FOREIGN KEY (`ModelId`) REFERENCES `model` (`Id`) ON UPDATE CASCADE ON DELETE CASCADE,
	CONSTRAINT `FK_ModelTag_TagId` FOREIGN KEY (`TagId`) REFERENCES `tag` (`Id`) ON UPDATE CASCADE ON DELETE CASCADE
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
;